import java.io.IOException;
import java.lang.*;
import java.sql.SQLException;
import java.util.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import org.xml.sax.*;
import java.io.File;

//read in an XML file and outsource it to mySQL
public class XMLReader
{
	//the source XML file
	private String filename;
	
	//the generated doc node
	private Document doc;
	
	static public HashMap<String, String> tableList = null;
	
	public XMLReader(String filename) throws Exception
	{
		this.filename = filename;
		
		linkFile();
		
		tableList = new HashMap<String, String>();
		
		tableList.put("bugContent", "(" +
				"bugid INT, " +
				"title VARCHAR(1000), " +
				"status VARCHAR(100), " +
				"owner VARCHAR(100), " +
				"closedOn VARCHAR(100), " +
				"type VARCHAR(100), " +
				"priority VARCHAR(100), " +
				"component VARCHAR(100), " +
				"stars INT, " +
				"reportedBy VARCHAR(100), " +
				"openedDate VARCHAR(100), " +
				"description VARCHAR(10000), " +
				//"PRIMARY KEY(bugid), " +
				"UNIQUE(bugid)"
				+ ")");
		
		tableList.put("bugComment", "(" +
				"bugid INT, " +
				"author VARCHAR(100), " +
				"whe VARCHAR(100), " +
				"wha VARCHAR(5000)"
				//"PRIMARY KEY(bugid)"
				+ ")");
	}
	
	private String escapeChar(String src)
	{
		String dst = src.replaceAll("\'", "&apos;");
		dst = dst.replaceAll("\"", "&quot;");
		dst = dst.replaceAll("\n", "");
		dst = dst.replaceAll("\t", "");	
		dst = dst.replace('\\', ' ');
		//String dst = src.replaceAll("^\^\", "\\\\");
		
		return dst;
	}
	
	//read in a file to generate a document node
	private void linkFile() throws ParserConfigurationException, IOException, 
		SAXException
	{
		//factory pattern
		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		
		//get a document builder
		DocumentBuilder db = null;
		
		try
		{
			db = dbf.newDocumentBuilder();
		}
		catch (ParserConfigurationException e)
		{
			e.printStackTrace();
		}
		
		//generate a document node from source file
		try
		{
			doc = db.parse(filename);
			
			doc.normalize();
		}
		catch (IOException e1)
		{
			e1.printStackTrace();
		}
		catch(SAXException e2)
		{
			e2.printStackTrace();
		}
	}
	
	//reset the XML file this class work with
	public void setFilename(String filename) throws Exception
	{
		this.filename = filename;
		
		linkFile();
	}
	
	public String getFilename()
	{
		return filename;
	}
	
	//get ride of annoying empty text node
	private boolean isEmptyTextNode(Node node)
	{
		if ((node.getNodeType() == Node.TEXT_NODE) ) 
		{
	        String value = node.getTextContent().trim();
	        if (value.equals("") ) 
	        {
	            return true;
	        }
	    }
		
		return false;
	}
	
	//check if it's comment node
	private boolean isCommentNode(Node node)
	{
		if (node.getNodeName().trim().equalsIgnoreCase("comment"))
		{
			return true;
		}
		
		return false;
	}
	
	//check if it's empty element
	private boolean isEmptyNode(Node node)
	{
        String value = node.getTextContent().trim();
        if (value.equals("") || value.equalsIgnoreCase("null")) 
        {
            return true;
        }
        
        return false;
	}
	
	
	void checkListSize(NodeList list)
	{
		if (list.getLength() != 1)
		{
			throw new RuntimeException("wrong bugid list");
		}
	}
	
	
	private String insertBugComment(String bugid, Element node)
	{
		String insertBugComment = new String();
		String key = new String("(bugid, ");
		
		insertBugComment = "('";
		
		insertBugComment += bugid;
		insertBugComment += "', ";
		
		NodeList authorList = node.getElementsByTagName("author");
		checkListSize(authorList);
		Node authorNode = authorList.item(0);
		if (!isEmptyTextNode(authorNode) && !isEmptyNode(authorNode))
		{
			insertBugComment += "'" +  escapeChar(authorNode.getTextContent().trim()) + "', ";
		
			key += "author, ";
		}
		
		NodeList whenList = node.getElementsByTagName("when");
		checkListSize(whenList);
		Node whenNode = whenList.item(0);
		if (!isEmptyTextNode(whenNode) && !isEmptyNode(whenNode))
		{
			insertBugComment += "'" +  escapeChar(whenNode.getTextContent().trim()) + "', ";
		
			key += "whe, ";
		}
		
		NodeList whatList = node.getElementsByTagName("what");
		checkListSize(whatList);
		Node wahtNode = whatList.item(0);
		if (!isEmptyTextNode(wahtNode) && !isEmptyNode(wahtNode))
		{
			insertBugComment += "'" +  escapeChar(wahtNode.getTextContent().trim()) + "', ";
		
			key += "wha, ";
		}
		
		key = key.substring(0, key.length() - 2);
		
		insertBugComment = insertBugComment.substring(0, insertBugComment.length() - 2);
		
		insertBugComment += ")";
		key += ") VALUES";
		
		return key + insertBugComment;
	}
	
	//partially generate the sq1 insertion statement
	private String insertBugContent(Node node)
	{
		//ArrayList<String> insertStatement = new ArrayList<String>();
		
		String insertKey = "(";
		String insertValue = "(";
		
		NodeList elementList = node.getChildNodes();
		for (int i = 0; i < elementList.getLength(); i++)
		{
			Node element = elementList.item(i);
			
			//skip the comment node and empty text node
			if (isEmptyTextNode(element) || isEmptyNode(element))
			{
				continue;
			}
			
			//skip empty element
			if (isCommentNode(element))
			{
				continue;
			}
			
			insertKey += element.getNodeName().trim();
			insertKey += ", ";
			
			insertValue += "'";
			insertValue +=  escapeChar(element.getTextContent().trim());
			insertValue += "', ";
		}
		insertKey = insertKey.substring(0, insertKey.length() - 2);
		insertValue = insertValue.substring(0, insertValue.length() - 2);
		
		insertKey += ")";
		insertValue += ")";
	
		return insertKey + " VALUES" + insertValue;
	}
	
	public void outsource(DB db) throws SQLException
	{
		Element root = doc.getDocumentElement();
		
		root.normalize();
		
		NodeList bugList = root.getChildNodes();
		
		//for test use
		//System.out.println("node length--> " + bugList.getLength());
		
		for (int i = 0; i < bugList.getLength(); i++)
		//for (int i = 0; i < 100; i++)
		{
			Node bug = bugList.item(i);
			
			bug.normalize();
			
			//skip the empty text node
			if (isEmptyTextNode(bug))
			{
				continue;
			}
			
			//partially generate the bug content insertion sql statement
			String insertBugContent = insertBugContent(bug);
			//System.out.println(insertBugContent);
			
			//insert the bug content into the db
			db.insertTable("bugContent", insertBugContent);
			
			//partially generate the bug comment insertion sql statement
			
			NodeList commentList = ((Element)bug).getElementsByTagName("comment");
			//skip the empty comments
			if (commentList.getLength() == 0)
			{
				continue;
			}
			
			
			//get bug id
			NodeList bugidList = ((Element)bug).getElementsByTagName("bugid");
			checkListSize(bugidList);
			Node bugidNode = bugidList.item(0);
			String bugid = bugidNode.getTextContent().trim();
			
			for (int j = 0; j < commentList.getLength(); j++)
			{	
				//String insertBugComment = new String("(bugid, author, whe, wha) VALUES");
				
				String insertBugComment = new String();
				
				insertBugComment += insertBugComment(bugid, (Element)commentList.item(j));
				
				//insertBugComment = insertBugComment.substring(0, insertBugComment.length()- 2);
				//System.out.println(insertBugComment);
				
				//insert the bug comment into the db
				db.insertTable("bugComment", insertBugComment);
			}
			
	

		}
	}
	
	public static void main(String args[]) throws Exception
	{
		File dir = new File("data");
		
		String filepath = dir.getAbsolutePath() + "/android_platform_bugs.xml";
		
		//for test use
		//System.out.println(filepath);
		
		DB db = new DB();
		
		db.connect();
		db.buildAll();
		
		XMLReader rd = new XMLReader(filepath);
		
		rd.outsource(db);
		
		//db.dropAll();
		db.close();
	}
}
